* metro stations, schools, parks, and hospitals in barcelona
********************************************************************************
* prelims
********************************************************************************
{
clear all
set more off
cap log close

}
********************************************************************************
* metro stations
********************************************************************************
{

* load data
import delimited "$orig/tmb/estacions_linia.csv", encoding(UTF-8)clear

* inaguration date
g year_start = substr(data_inauguracio,1,4)
g month_start = substr(data_inauguracio,6,2)
g day_start = substr(data_inauguracio,9,2)
destring *_start, force replace
g inauguration = mdy(month_start,day_start,year_start)
format inauguration %td
la var inauguration "Imauguration date"
drop data_ina*

* lat and long
g lon = substr(geometry,8,length(geometry)-9)
g lat = substr(lon, strpos(lon, " ")+1, length(lon)-strpos(lon, " "))
replace lon = substr(lon,1,strpos(lon, " "))
replace lon = subinstr(lon," ","",.)

* label
la var lat "Latitude"
la var lon "Longitude"
drop geometry

* store
compress
save  "$data/int/metro_stations.dta", replace	
	
}
********************************************************************************
* schools
********************************************************************************
{
* load data
import delimited "$orig/aj_barcelona/schools.csv", encoding(UTF-16) clear

* construction year
g constyear = substr(created,1,4)
destring constyear, replace
drop created

keep register_id name constyear addresses_road_id addresses_road_name address* *lat *lon secondary_filters_name

rename geo_epgs_4326_lat lat
rename geo_epgs_4326_lon lon

drop if lat==.
drop if lat < 40 

* identify schools
g school = (regexm(secondary_filters_name,"Educ|Enseny|Esc")==1)

* store
compress
save "$data/int/bcn_schools.dta", replace

}
********************************************************************************
* parks
********************************************************************************
{
	
* load data
import delimited "$orig/aj_barcelona/parcs-i-jardins.csv", encoding(UTF-16) clear

* construction year
g constyear = substr(created,1,4)
destring constyear, replace
drop created

keep register_id name constyear addresses_road_id addresses_road_name address* *lat *lon

* rename
rename geo_epgs_4326_lat lat
rename geo_epgs_4326_lon lon

* store
compress
save "$data/int/bcn_parks.dta", replace

}
********************************************************************************
* hospitals
********************************************************************************
{
	
* load data
import delimited "$orig/aj_barcelona/bcn_hospitals.csv", encoding(UTF-16) clear

* construction year
g constyear = substr(created,1,4)
destring constyear, replace
drop created
	
keep register_id name constyear addresses_road_id addresses_road_name address* *lat *lon

* rename
rename geo_epgs_4326_lat lat
rename geo_epgs_4326_lon lon

drop if lat==.
drop if lat < 40 

* store
compress
save "$data/int/bcn_hospitals.dta", replace
	
}
********************************************************************************
* closing
********************************************************************************
{

cap log close
clear 

}
